




ALTER   function fn_get_locations_up_to_test_by_id(@process_step_id bigint)
returns  @fn_result Table(ProcessStep_ID bigint, 
			Location_ID int,
			Location_Name varchar(50))
as
begin
	declare @location_id int
	declare @location_name varchar(50)
	declare @ps_type varchar(20)
	declare @ps_type_name varchar(20)
	declare @temp_process_step_id bigint
	declare @location_cursor CURSOR
	declare @process_step_cursor CURSOR
	declare @deallocate_process_step_cursor int
	declare @result Table(ProcessStep_ID bigint, 
			Location_ID int,
			Location_Name varchar(50))	

	
	set @process_step_cursor = CURSOR FOR
	select *from fn_get_process_flow_forward_by_id( @process_step_id )
	open @process_step_cursor
		fetch next from @process_step_cursor into @temp_process_step_id, @ps_type, @ps_type_name
		while @@fetch_status = 0
		begin
			if @ps_type_name <> 'TEST'
			begin
				set @location_cursor = CURSOR FOR
				select Location_ID from Process_Step_Location_Table where ProcessStep_ID = @temp_process_step_id
				open @location_cursor
					fetch next from @location_cursor into @location_id
					while @@fetch_status = 0
					begin
						select @location_name = @ps_type+'-'+l.L_Vendor_Name+'/'+a.City from Location l, Address a where Location_ID =@location_id and l.Address_ID = a.Address_ID
						insert @result(ProcessStep_ID, Location_ID, Location_Name) values(@temp_process_step_id, @location_id, @location_name)
						fetch next from @location_cursor into @location_id
					end
				close @location_cursor
				deallocate @location_cursor
			end
			else
			begin
				set @location_cursor = CURSOR FOR
				select Location_ID from Process_Step_Location_Table where ProcessStep_ID = @temp_process_step_id
				open @location_cursor
					fetch next from @location_cursor into @location_id
					while @@fetch_status = 0
					begin
						select @location_name = @ps_type+'-'+l.L_Vendor_Name+'/'+a.City from Location l, Address a where Location_ID =@location_id and l.Address_ID = a.Address_ID
						insert @result(ProcessStep_ID, Location_ID, Location_Name) values(@temp_process_step_id, @location_id, @location_name)
						fetch next from @location_cursor into @location_id
					end
				close @location_cursor
				deallocate @location_cursor				
				close @process_step_cursor
				deallocate @process_step_cursor
				set @deallocate_process_step_cursor = 1
				break;
			end
			fetch next from @process_step_cursor into @temp_process_step_id, @ps_type, @ps_type_name
		end
	if @deallocate_process_step_cursor <> 1
	begin 
		close @process_step_cursor
		deallocate @process_step_cursor
	end 

	insert @fn_result select *from @result
	return
end



